﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// 数据访问类:WorkTime
    /// </summary>
    public partial class WorkTime
    {
        /// <summary>
        /// 工作时间定义
        /// </summary>
        public WorkTime()
        { }
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="fWorkTimeID">编号</param>
        /// <param name="fEntityNo">单位编号</param>
        /// <returns>是否存在该数据</returns>
        public bool Exists(decimal fWorkTimeID, string fEntityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_WorkTime");
            strSql.Append(" where FWorkTimeID=@FWorkTimeID and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FWorkTimeID", SqlDbType.Decimal,13),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = fWorkTimeID;
            parameters[1].Value = fEntityNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取上下班时间
        /// </summary>
        /// <param name="date">需获取的哪天的上下班时间</param>
        /// <param name="type">上班时间或下班时间</param>
        /// <param name="amorpm">上午或下午</param>
        /// <returns>上下班时间</returns>
        public string GetTime(string date, string type,string amorpm)
        {
            StringBuilder strSql = new StringBuilder();
            if (type == "Bregin")
            {
                if (amorpm == "上午")
                {
                    strSql.Append("SELECT FAMBeginTime FROM dbo.t_WorkTime WHERE DATEDIFF(DAY,FBeginDate,@date)>=0 AND  DATEDIFF(DAY,FEndDate,@date)<=0");
                }
                else
                {
                    strSql.Append("SELECT FPMBeginTime FROM dbo.t_WorkTime WHERE DATEDIFF(DAY,FBeginDate,@date)>=0 AND  DATEDIFF(DAY,FEndDate,@date)<=0");
                }
            }
            else
            {
                if (amorpm == "上午")
                {
                    strSql.Append("SELECT FAMEndTime FROM dbo.t_WorkTime WHERE DATEDIFF(DAY,FBeginDate,@date)>=0 AND  DATEDIFF(DAY,FEndDate,@date)<=0");
                }
                else
                {
                    strSql.Append("SELECT FPMEndTime FROM dbo.t_WorkTime WHERE DATEDIFF(DAY,FBeginDate,@date)>=0 AND  DATEDIFF(DAY,FEndDate,@date)<=0");
                }
            }
            SqlParameter[] parameters = {
					new SqlParameter("@date", SqlDbType.DateTime)			};
            parameters[0].Value = date;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);

            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }


        /// <summary>
        /// 更具日期获取改天的上下班时间
        /// </summary>
        /// <param name="date">需获取的日期</param>
        /// <returns>实体类</returns>
        public Model.WorkTime GetModelByDate(string date)
        {
            StringBuilder strSql = new StringBuilder();

           strSql.Append(@"SELECT  FWorkTimeID ,
                                   FEntityNo ,
                                   FBeginDate ,
                                   FEndDate ,
                                   FAMBeginTime ,
                                   FAMEndTime ,
                                   FPMBeginTime ,
                                   FPMEndTime,
                                   FAMAttendanceTime,
                                   FPMAttendanceTime,
                                   FIsModify FROM dbo.t_WorkTime WHERE DATEDIFF(DAY,FBeginDate,@date)>=0 AND  DATEDIFF(DAY,FEndDate,@date)<=0");
      

            SqlParameter[] parameters = {
					new SqlParameter("@date", SqlDbType.DateTime)};
            parameters[0].Value = date;

            ElingOA6_Admin_DSJCJ.Model.WorkTime model = new ElingOA6_Admin_DSJCJ.Model.WorkTime();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        /// <param name="tableName">表明</param>
        /// <param name="fields">显示字段</param>
        /// <param name="pageSize">每页多少条数据</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="fOrderFiled">排序字段</param>
        /// <param name="orderType">排序方式</param>
        /// <param name="total">总记录数</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetWorkTimeListByPage(string tableName, string fields, int pageSize, int pageIndex, string strWhere, string fOrderFiled, int orderType, out int total)
        {
            SqlParameter[] parameters = {  
                    new SqlParameter("@tableName", SqlDbType.VarChar, 50),  
                    new SqlParameter("@fields", SqlDbType.VarChar, 2000),  
                    new SqlParameter("@orderField", SqlDbType.VarChar, 500),  
                    new SqlParameter("@sqlWhere", SqlDbType.VarChar,2000),  
                    new SqlParameter("@pageSize", SqlDbType.Int),  
                    new SqlParameter("@pageIndex", SqlDbType.Int),  
                    new SqlParameter("@orderType", SqlDbType.Int),  
                    new SqlParameter("@TotalPage", SqlDbType.Int),
                    new SqlParameter("@TotalRecordCount", SqlDbType.Int)  
                };

            parameters[0].Value = tableName;
            parameters[1].Value = fields;
            parameters[2].Value = fOrderFiled;
            parameters[3].Value = strWhere;
            parameters[4].Value = pageSize;
            parameters[5].Value = pageIndex;
            parameters[6].Value = orderType;
            parameters[7].Direction = ParameterDirection.Output;
            parameters[8].Direction = ParameterDirection.Output;
            DataSet ds = DbHelperSQL.RunProcedure("up_GetRecordByPage", parameters, "ds", 60 * 30);
            total = int.Parse(parameters[8].Value.ToString());
            return ds;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">增加实体类</param>
        /// <returns>是否增加成功</returns>
        public decimal Add(ElingOA6_Admin_DSJCJ.Model.WorkTime model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_WorkTime(");
            strSql.Append("FEntityNo,FBeginDate,FEndDate,FAMBeginTime,FAMEndTime,FPMBeginTime,FPMEndTime,FAMAttendanceTime,FPMAttendanceTime,FIsModify)");
            strSql.Append(" values (");
            strSql.Append("@FEntityNo,@FBeginDate,@FEndDate,@FAMBeginTime,@FAMEndTime,@FPMBeginTime,@FPMEndTime,@FAMAttendanceTime,@FPMAttendanceTime,@FIsModify)");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FBeginDate", SqlDbType.DateTime),
					new SqlParameter("@FEndDate", SqlDbType.DateTime),
					new SqlParameter("@FAMBeginTime", SqlDbType.NVarChar,40),
					new SqlParameter("@FAMEndTime", SqlDbType.NVarChar,40),
					new SqlParameter("@FPMBeginTime", SqlDbType.NVarChar,40),
					new SqlParameter("@FPMEndTime", SqlDbType.NVarChar,40),
                    new SqlParameter("@FAMAttendanceTime", SqlDbType.NVarChar,40),
					new SqlParameter("@FPMAttendanceTime", SqlDbType.NVarChar,40),
                    new SqlParameter("@FIsModify", SqlDbType.NVarChar,1)};
            parameters[0].Value = model.FEntityNo;
            parameters[1].Value = model.FBeginDate;
            parameters[2].Value = model.FEndDate;
            parameters[3].Value = model.FAMBeginTime.ToString("HH:mm");
            parameters[4].Value = model.FAMEndTime.ToString("HH:mm");
            parameters[5].Value = model.FPMBeginTime.ToString("HH:mm");
            parameters[6].Value = model.FPMEndTime.ToString("HH:mm");
            parameters[7].Value = model.FAMAttendanceTime.ToString("HH:mm");
            parameters[8].Value = model.FPMAttendanceTime.ToString("HH:mm");
            parameters[9].Value = model.FIsModify;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToDecimal(obj);
            }
        }
        
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model">更新实体类</param>
        /// <returns>是否更新成功</returns>
        public bool Update(ElingOA6_Admin_DSJCJ.Model.WorkTime model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_WorkTime set ");
            strSql.Append("FBeginDate=@FBeginDate,");
            strSql.Append("FEndDate=@FEndDate,");
            strSql.Append("FAMBeginTime=@FAMBeginTime,");
            strSql.Append("FAMEndTime=@FAMEndTime,");
            strSql.Append("FPMBeginTime=@FPMBeginTime,");
            strSql.Append("FPMEndTime=@FPMEndTime,");
            strSql.Append("FAMAttendanceTime=@FAMAttendanceTime,");
            strSql.Append("FPMAttendanceTime=@FPMAttendanceTime,");
            strSql.Append("FIsModify=@FIsModify");
            strSql.Append(" where FWorkTimeID=@FWorkTimeID");
            SqlParameter[] parameters = {
					new SqlParameter("@FBeginDate", SqlDbType.DateTime),
					new SqlParameter("@FEndDate", SqlDbType.DateTime),
					new SqlParameter("@FAMBeginTime", SqlDbType.DateTime,3),
					new SqlParameter("@FAMEndTime", SqlDbType.DateTime,3),
					new SqlParameter("@FPMBeginTime", SqlDbType.DateTime,3),
					new SqlParameter("@FPMEndTime", SqlDbType.DateTime,3),
                    new SqlParameter("@FAMAttendanceTime", SqlDbType.NVarChar,40),
					new SqlParameter("@FPMAttendanceTime", SqlDbType.NVarChar,40),
                    new SqlParameter("@FIsModify", SqlDbType.Char,1),
					new SqlParameter("@FWorkTimeID", SqlDbType.Decimal,13)};
            parameters[0].Value = model.FBeginDate;
            parameters[1].Value = model.FEndDate;
            parameters[2].Value = model.FAMBeginTime;
            parameters[3].Value = model.FAMEndTime;
            parameters[4].Value = model.FPMBeginTime;
            parameters[5].Value = model.FPMEndTime;
            parameters[6].Value = model.FAMAttendanceTime;
            parameters[7].Value = model.FPMAttendanceTime;
            parameters[8].Value = model.FIsModify;
            parameters[9].Value = model.FWorkTimeID;         

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="fWorkTimeID">编号</param>
        /// <returns>是否删除成功</returns>
        public bool Delete(decimal fWorkTimeID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_WorkTime ");
            strSql.Append(" where FWorkTimeID=@FWorkTimeID");
            SqlParameter[] parameters = {
					new SqlParameter("@FWorkTimeID", SqlDbType.Decimal)
			};
            parameters[0].Value = fWorkTimeID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        
        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="fWorkTimeID">编号</param>
        /// <param name="fEntityNo">公司编号</param>
        /// <returns>是否删除成功</returns>
        public bool Delete(decimal fWorkTimeID, string fEntityNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_WorkTime ");
            strSql.Append(" where FWorkTimeID=@FWorkTimeID and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FWorkTimeID", SqlDbType.Decimal,13),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = fWorkTimeID;
            parameters[1].Value = fEntityNo;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        
        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="fWorkTimeIDlist">编号列表以“，”分隔</param>
        /// <returns>是否删除成功</returns>
        public bool DeleteList(string fWorkTimeIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_WorkTime ");
            strSql.Append(" where FWorkTimeID in (" + fWorkTimeIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        ///  得到一个对象实体
        /// </summary>
        /// <param name="fWorkTimeID">编号</param>
        /// <returns>实体类</returns>
        public ElingOA6_Admin_DSJCJ.Model.WorkTime GetModel(decimal fWorkTimeID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FWorkTimeID,FEntityNo,FBeginDate,FEndDate,FAMBeginTime,FAMEndTime,FPMBeginTime,FPMEndTime,FAMAttendanceTime,FPMAttendanceTime,FIsModify from t_WorkTime ");
            strSql.Append(" where FWorkTimeID=@FWorkTimeID");
            SqlParameter[] parameters = {
					new SqlParameter("@FWorkTimeID", SqlDbType.Decimal)
			};
            parameters[0].Value = fWorkTimeID;

            ElingOA6_Admin_DSJCJ.Model.WorkTime model = new ElingOA6_Admin_DSJCJ.Model.WorkTime();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        ///  将DataRow转换为实体对象
        /// </summary>
        /// <param name="row">DataRow对象</param>
        /// <returns>实体对象</returns>
        public ElingOA6_Admin_DSJCJ.Model.WorkTime DataRowToModel(DataRow row)
        {
            ElingOA6_Admin_DSJCJ.Model.WorkTime model = new ElingOA6_Admin_DSJCJ.Model.WorkTime();
            if (row != null)
            {
                if (row["FWorkTimeID"] != null && row["FWorkTimeID"].ToString() != "")
                {
                    model.FWorkTimeID = decimal.Parse(row["FWorkTimeID"].ToString());
                }
                if (row["FEntityNo"] != null)
                {
                    model.FEntityNo = row["FEntityNo"].ToString();
                }
                if (row["FBeginDate"] != null && row["FBeginDate"].ToString() != "")
                {
                    model.FBeginDate = DateTime.Parse(row["FBeginDate"].ToString());
                }
                if (row["FEndDate"] != null && row["FEndDate"].ToString() != "")
                {
                    model.FEndDate = DateTime.Parse(row["FEndDate"].ToString());
                }
                if (row["FAMBeginTime"] != null && row["FAMBeginTime"].ToString() != "")
                {
                    model.FAMBeginTime = DateTime.Parse(row["FAMBeginTime"].ToString());
                }
                if (row["FAMEndTime"] != null && row["FAMEndTime"].ToString() != "")
                {
                    model.FAMEndTime = DateTime.Parse(row["FAMEndTime"].ToString());
                }
                if (row["FPMBeginTime"] != null && row["FPMBeginTime"].ToString() != "")
                {
                    model.FPMBeginTime = DateTime.Parse(row["FPMBeginTime"].ToString());
                }
                if (row["FPMEndTime"] != null && row["FPMEndTime"].ToString() != "")
                {
                    model.FPMEndTime = DateTime.Parse(row["FPMEndTime"].ToString());
                }
                if (row["FAMAttendanceTime"] != null && row["FAMAttendanceTime"].ToString() != "")
                {
                    model.FAMAttendanceTime = DateTime.Parse(row["FAMAttendanceTime"].ToString());
                }
                if (row["FPMAttendanceTime"] != null && row["FPMAttendanceTime"].ToString() != "")
                {
                    model.FPMAttendanceTime = DateTime.Parse(row["FPMAttendanceTime"].ToString());
                }
                if (row["FIsModify"] != null)
                {
                    model.FIsModify = row["FIsModify"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FWorkTimeID,FEntityNo,FBeginDate,FEndDate,FAMBeginTime,FAMEndTime,FPMBeginTime,FPMEndTime ");
            strSql.Append(" FROM t_WorkTime ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        /// <param name="top">前几行数据</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="filedOrder">排序方式</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetList(int top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (top > 0)
            {
                strSql.Append(" top " + top.ToString());
            }
            strSql.Append(" FWorkTimeID,FEntityNo,FBeginDate,FEndDate,FAMBeginTime,FAMEndTime,FPMBeginTime,FPMEndTime ");
            strSql.Append(" FROM t_WorkTime ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns>记录总数</returns>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_WorkTime ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

      
        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

